select /*+ SET_VAR(exec_mem_limit = 3669654427) */
    ad_disptype
    ,business_line
    ,platform
    ,scate2
    ,sloc1
    ,slot
    ,search_pv
    ,search_uv
    ,search_as
    ,search_epv
    ,round(if(search_as / search_epv is null , 0 , search_as / search_epv),5) as search_asn
from (
    select
        ad_disptype
        ,business_line
        ,platform
        ,scate2
        ,sloc1
        ,slot
        ,APPROX_COUNT_DISTINCT(recsid) as search_pv
        ,APPROX_COUNT_DISTINCT(case when platform = '3' and length(imei) > 5 then imei when platform in('1','2') and length(cookieid) >5 then cookieid else null end) as search_uv
        ,APPROX_COUNT_DISTINCT(concat_ws(',',recsid,slot,ad_id)) as search_as
        ,APPROX_COUNT_DISTINCT(if(parthasinfo=1,recsid,null)) search_epv
        -- ,APPROX_COUNT_DISTINCT(concat_ws(',',recsid,slot,ad_id)) /APPROX_COUNT_DISTINCT(if(parthasinfo=1,recsid,null))  as search_asn
    from (
        SELECT
            case WHEN ad_disptype = NULL then '-' else ad_disptype END as ad_disptype
            ,case WHEN  business_line = NULL then '-' else business_line end as business_line
            ,case WHEN  platform = NULL then '-' else platform end as platform
            ,case WHEN  scate2 = NULL  then '-' else scate2 end as scate2
            ,case WHEN  sloc1 = NULL  then '-' else sloc1 end as sloc1
            ,case WHEN  slot = NULL then '-' else slot end as slot
            ,recsid
            ,parthasinfo
            ,imei_md5 as imei
            ,cookieid
            ,ad_id
        from hdp_lbg_supin_dwd_zp_search
        where (e_time) >= '%s' and (e_time) <= '%s'
    ) tmp
    GROUP by
    GROUPING SETS (
        (ad_disptype)
        ,(business_line)
        ,(platform)
        ,(scate2)
        ,(sloc1)
        ,(slot)
                    ,()
        ,(ad_disptype,business_line)
        ,(ad_disptype,platform)
        ,(business_line,platform)
        ,(ad_disptype,scate2)
        ,(business_line,scate2)
        ,(platform,scate2)
        ,(ad_disptype,sloc1)
        ,(business_line,sloc1)
        ,(platform,sloc1)
        ,(scate2,sloc1)
        ,(ad_disptype,slot)
        ,(business_line,slot)
        ,(platform,slot)
        ,(scate2,slot)
        ,(sloc1,slot)
        ,(business_line,platform,slot)
        ,(ad_disptype,business_line,platform,slot)
        ,(business_line,platform,scate2,slot)
        ,(business_line,platform,sloc1,slot)
        ,(ad_disptype,business_line,platform,scate2)
        ,(ad_disptype,business_line,platform,sloc1)
        ,(ad_disptype,business_line,platform,scate2,slot)
        ,(ad_disptype,business_line,platform,sloc1,slot)
        ,(ad_disptype,business_line,platform,scate2,sloc1,slot)
    )
    HAVING(
        if(ad_disptype is null ,'',ad_disptype) != '-'
        and if(business_line is null ,'',business_line) != '-'
        and if(platform is null ,'',platform) != '-'
        and if(scate2 is null ,'',scate2) != '-'
        and if(sloc1 is null ,'',sloc1) != '-'
        and if(slot is null ,'',slot) != '-'
    )
) tmp